Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


Note that the SimpleText driver supports only character, integer, and binary data; thus, CommonValue only accepts these data types, and only attempts to convert data to these same types. A more robust driver would need to further implement this class to include more (if not all) data types.

Escape Clauses

Another consideration when implementing a JDBC driver is processing escape clauses. Escape clauses are used as extensions to SQL and provide a method to perform DBMS-specific extensions, which are interoperable among DBMSes. The JDBC driver must accept escape clauses and expand them into the native DBMS format before processing the SQL statement. While this sounds simple enough on the surface, this process may turn out to be an enormous task. If you are developing a driver that uses an existing DBMS, and the JDBC driver simply passes SQL statements to the DBMS, you may have to develop a parser to scan for escape clauses.

The following types of SQL extensions are defined:

  Date, time, and timestamp data
  Scalar functions such as numeric, string, and data type conversion
  LIKE predicate escape characters
  Outer joins
  Procedures

The JDBC specification does not directly address escape clauses; they are inherited from the ODBC specification. The syntax defined by ODBC uses the escape clause provided by the X/OPEN and SQL Access Group SQL CAE specification (1992). The general syntax for an escape clause is:

{escape}

We’ll cover the specific syntax for each type of escape clause in the following sections.

Date, Time, And Timestamp

The date, time, and timestamp escape clauses allow an application to specify date, time, and timestamp data in a uniform manner, without concern to the native DBMS format (for which the JDBC driver is responsible). The syntax for each (respectively) is

{d 'value'}
{t 'value'}
{ts 'value'}

where d indicates value is a date in the format yyyy-mm-dd, t indicates value is a time in the format hh:mm:ss, and ts indicates value is a timestamp in the format yyyy-mm-dd hh:mm:ss[.f...]. The following SQL statements illustrate the use of each:

UPDATE EMPLOYEE SET HIREDATE={d '1992-04-01'}
UPDATE EMPLOYEE SET LAST_IN={ts '1996-07-03 08:00:00'}
UPDATE EMPLOYEE SET BREAK_DUE={t '10:00:00'}

Scalar Functions

The five types of scalar functions—string, numeric, time and date, system, and data type conversion—all use the syntax:

{fn    scalar-function}

To determine what type of string functions a JDBC driver supports, an application can use the DatabaseMetaData method getStringFunctions. This method returns a comma-separated list of string functions, possibly containing ASCII, CHAR, CONCAT, DIFFERENCE, INSERT, LCASE, LEFT, LENGTH, LOCATE, LTRIM, REPEAT, REPLACE, RIGHT, RTRIM, SOUNDEX, SPACE, SUBSTRING, and/or UCASE.

To determine what type of numeric functions a JDBC driver supports, an application can use the DatabaseMetaData method getNumericFunctions. This method returns a comma-separated list of numeric functions, possibly containing ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, MOD, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN, and/or TRUNCATE.

To determine what type of system functions a JDBC driver supports, an application can use the DatabaseMetaData method getSystemFunctions. This method returns a comma-separated list of system functions, possibly containing DATABASE, IFNULL, and/or USER.

To determine what type of time and date functions a JDBC driver supports, an application can use the DatabaseMetaData method getTimeDateFunctions. This method returns a comma-separated list of time and date functions, possibly containing CURDATE, CURTIME, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, NOW, QUARTER, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, WEEK, and/or YEAR.

To determine what type of explicit data type conversions a JDBC driver supports, an application can use the DatabaseMetaData method supportsConvert. This method has two parameters: a from SQL data type and a to SQL data type. If the explicit data conversion between the two SQL types is supported, the method returns true. The syntax for the CONVERT function is

{fn  CONVERT(value,  data_type)}

where value is a column name, the result of another scalar function, or a literal, and data_type is one of the JDBC SQL types listed in the Types class.

LIKE Predicate Escape Characters

In a LIKE predicate, the “%” (percent character) matches zero or more of any character, and the “_” (underscore character) matches any one character. In some instances, an SQL query may have the need to search for one of these special matching characters. In such cases, you can use the “%” and “_” characters as literals in a LIKE predicate by preceding them with an escape character. The DatabaseMetaData method getSearch-StringEscape returns the default escape character (which for most DBMSes will be the backslash character “ \”). To override the escape character, use the following syntax:

{escape 'escape-character'}

The following SQL statement uses the LIKE predicate escape clause to search for any columns that start with the “%” character:

SELECT * FROM EMPLOYEE WHERE NAME LIKE '\%' {escape '\'}

Outer Joins

JDBC supports the ANSI SQL-92 LEFT OUTER JOIN syntax. The escape clause syntax is

{oj  outer-join}

where outer-join is the table-reference LEFT OUTER JOIN {table-reference | outer-join} ON search-condition.

Procedures

A JDBC application can call a procedure in place of an SQL statement. The escape clause used for calling a procedure is

{[?=]  call procedure-name[(param[, param]...)]}

where procedure-name specifies the name of a procedure stored on the data source, and param specifies procedure parameters. A procedure can have zero or more parameters, and may return a value.

The JDBC Interfaces

Now let’s take a look at each of the JDBC interfaces, which are shown in Figure 10.2. We’ll go over the major aspects of each interface and use code examples from our SimpleText project whenever applicable. You should understand the JDBC API specification before attempting to create a JDBC driver; this section is meant to enhance the specification, not to replace it.


Figure 10.2  The JDBC interfaces.


Previous Table of Contents Next